﻿using ExcelTools.BaseModels;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelTools
{
    public static class ExportWithOutTemplate
    {
        /// <summary>
        /// DataTable数据导出CSV
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="exportPath"></param>
        public static void DataTableToCSV(DataTable dt, string exportPath)
        {

            FileStream fs = new FileStream(exportPath, FileMode.OpenOrCreate);
            StreamWriter sw = new StreamWriter(fs, Encoding.Default);

            string colStr = string.Empty;
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                colStr += i == 0 ? "" : "," + dt.Columns[i];
            }
            sw.WriteLine(colStr);
            foreach (DataRow item in dt.Rows)
            {
                string rowStr = string.Empty;
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    rowStr += i == 0 ? "" : "," + item[i];
                }
                sw.WriteLine(rowStr);
            }
            sw.Close();
        }

        public static void FillSheet(Dictionary<string, ICellStyle> titleStyles, Dictionary<string, ICellStyle> colStyles, Dictionary<string, ICellStyle> styles,
            ISheet tSheet, DataTable dt,
           int offsetCol = 0, int offsetRow = 0, bool titleFlag = false)
        {
            //初始行数
            var initRowIndex = titleFlag ? (offsetRow + 1) : (offsetRow + 0);

            if (titleFlag)
            {
                IRow titleRow = tSheet.GetRow(offsetRow);
                if (titleRow == null)
                {
                    titleRow = tSheet.CreateRow(offsetRow);
                }

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = titleRow.GetCell(offsetCol + j);
                    if (cell == null)
                    {
                        cell = titleRow.CreateCell(offsetCol + j);
                    }
                    if (titleStyles.Count == 1)
                    {
                        cell.CellStyle = titleStyles["1111"];
                    }
                    else
                    {
                        if (j == 0)
                        {
                            cell.CellStyle = titleStyles["1011"];
                        }
                        else if (j == dt.Columns.Count - 1)
                        {
                            cell.CellStyle = titleStyles["1110"];
                        }
                        else
                        {
                            cell.CellStyle = titleStyles["1010"];
                        }
                    }
                    cell.SetCellValue(dt.TableName);
                }

                var mergedRegion = new CellRangeAddress(offsetRow, offsetRow, titleRow.FirstCellNum, titleRow.LastCellNum - 1);
                tSheet.AddMergedRegion(mergedRegion);
            }

            //添加表头
            IRow headerRow = tSheet.GetRow(initRowIndex);
            if (headerRow == null)
            {
                headerRow = tSheet.CreateRow(initRowIndex);
            }
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                var cell = headerRow.GetCell(offsetCol + j);
                if (cell == null)
                {
                    cell = headerRow.CreateCell(offsetCol + j);
                }
                if (colStyles.Count == 1)
                {
                    cell.CellStyle = colStyles["1111"];
                }
                else
                {
                    if (j == 0)
                    {
                        cell.CellStyle = colStyles["1011"];
                    }
                    else if (j == dt.Columns.Count - 1)
                    {
                        cell.CellStyle = colStyles["1110"];
                    }
                    else
                    {
                        cell.CellStyle = colStyles["1010"];
                    }
                }
                cell.SetCellValue(dt.Columns[j].ToString());
            }

            //循环添加每一行数据
            for (int j = 0; j < dt.Rows.Count; j++)
            {
                IRow tRow = tSheet.GetRow(initRowIndex + j + 1);
                if (tRow == null)
                {
                    tRow = tSheet.CreateRow(initRowIndex + j + 1);
                }

                for (int k = 0; k < dt.Columns.Count; k++)
                {
                    var cell = tRow.GetCell(offsetCol + k);
                    if (cell == null)
                    {
                        cell = tRow.CreateCell(offsetCol + k);
                    }

                    if (styles.Count == 1)
                    {
                        cell.CellStyle = styles["1111"];
                    }
                    else
                    {
                        if (j == 0)
                        {
                            if (k == 0)
                            {
                                cell.CellStyle = styles["1001"];
                            }
                            else if (k == dt.Columns.Count - 1)
                            {
                                cell.CellStyle = styles["0110"];
                            }
                            else
                            {
                                cell.CellStyle = styles["1000"];
                            }
                        }
                        else if (j == dt.Rows.Count - 1)
                        {
                            if (k == 0)
                            {
                                cell.CellStyle = styles["0001"];
                            }
                            else if (k == dt.Columns.Count - 1)
                            {
                                cell.CellStyle = styles["0100"];
                            }
                            else
                            {
                                cell.CellStyle = styles["0000"];
                            }
                        }
                        else
                        {
                            if (k == 0)
                            {
                                cell.CellStyle = styles["0011"];
                            }
                            else if (k == dt.Columns.Count - 1)
                            {
                                cell.CellStyle = styles["0010"];
                            }
                            else
                            {
                                cell.CellStyle = styles["0110"];
                            }
                        }
                    }
                    var cellVal = dt.Rows[j][k].ToString();
                    cell.SetCellValue(cellVal);
                }
            }

            for (int j = 0; j < dt.Columns.Count; j++)
            {
                tSheet.AutoSizeColumn(offsetCol + j);
            }
        }


        public static Dictionary<string, ICellStyle> CreateICellStyle(this IWorkbook wb, CellStyle cellStyle = null)
        {
            if (cellStyle == null)
            {
                cellStyle = new CellStyle()
                {
                    FontIsBold = false,
                    FontName = "等线",
                    BorderType = BorderType.Common
                };
            }

            ICellStyle baseCellStyle = wb.CreateCellStyle();
            if (!string.IsNullOrEmpty(cellStyle.FontName))
            {
                IFont fontStyle = wb.CreateFont();
                fontStyle.IsBold = cellStyle.FontIsBold;
                fontStyle.FontName = cellStyle.FontName;
                baseCellStyle.SetFont(fontStyle);
            }


            var rtnDic = new Dictionary<string, ICellStyle>();

            switch (cellStyle.BorderType)
            {
                case BorderType.Common:
                    baseCellStyle.BorderTop = BorderStyle.Thin;
                    baseCellStyle.BorderRight = BorderStyle.Thin;
                    baseCellStyle.BorderBottom = BorderStyle.Thin;
                    baseCellStyle.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("1111", baseCellStyle);
                    break;
                case BorderType.CellBorderBlod:
                    baseCellStyle.BorderTop = BorderStyle.Medium;
                    baseCellStyle.BorderRight = BorderStyle.Medium;
                    baseCellStyle.BorderBottom = BorderStyle.Medium;
                    baseCellStyle.BorderLeft = BorderStyle.Medium;
                    rtnDic.Add("1111", baseCellStyle);
                    break;
                case BorderType.RowBorderBlod:

                    #region 行列数均大于1
                    var cellStyle0000 = wb.CreateCellStyle();
                    cellStyle0000.CloneStyleFrom(baseCellStyle);
                    cellStyle0000.BorderTop = BorderStyle.Thin;
                    cellStyle0000.BorderRight = BorderStyle.Thin;
                    cellStyle0000.BorderBottom = BorderStyle.Thin;
                    cellStyle0000.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("0000", cellStyle0000);

                    var cellStyle1001 = wb.CreateCellStyle();
                    cellStyle1001.CloneStyleFrom(baseCellStyle);
                    cellStyle1001.BorderTop = BorderStyle.Medium;
                    cellStyle1001.BorderRight = BorderStyle.Thin;
                    cellStyle1001.BorderBottom = BorderStyle.Thin;
                    cellStyle1001.BorderLeft = BorderStyle.Medium;
                    rtnDic.Add("1001", cellStyle1001);

                    var cellStyle1000 = wb.CreateCellStyle();
                    cellStyle1000.CloneStyleFrom(baseCellStyle);
                    cellStyle1000.BorderTop = BorderStyle.Medium;
                    cellStyle1000.BorderRight = BorderStyle.Thin;
                    cellStyle1000.BorderBottom = BorderStyle.Thin;
                    cellStyle1000.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("1000", cellStyle1000);

                    var cellStyle1100 = wb.CreateCellStyle();
                    cellStyle1100.CloneStyleFrom(baseCellStyle);
                    cellStyle1100.BorderTop = BorderStyle.Medium;
                    cellStyle1100.BorderRight = BorderStyle.Thin;
                    cellStyle1100.BorderBottom = BorderStyle.Thin;
                    cellStyle1100.BorderLeft = BorderStyle.Medium;
                    rtnDic.Add("1100", cellStyle1100);


                    var cellStyle0100 = wb.CreateCellStyle();
                    cellStyle0100.CloneStyleFrom(baseCellStyle);
                    cellStyle0100.BorderTop = BorderStyle.Thin;
                    cellStyle0100.BorderRight = BorderStyle.Medium;
                    cellStyle0100.BorderBottom = BorderStyle.Thin;
                    cellStyle0100.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("0100", cellStyle0100);

                    var cellStyle0110 = wb.CreateCellStyle();
                    cellStyle0110.CloneStyleFrom(baseCellStyle);
                    cellStyle0110.BorderTop = BorderStyle.Thin;
                    cellStyle0110.BorderRight = BorderStyle.Medium;
                    cellStyle0110.BorderBottom = BorderStyle.Medium;
                    cellStyle0110.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("0110", cellStyle0110);

                    var cellStyle0010 = wb.CreateCellStyle();
                    cellStyle0010.CloneStyleFrom(baseCellStyle);
                    cellStyle0010.BorderTop = BorderStyle.Thin;
                    cellStyle0010.BorderRight = BorderStyle.Thin;
                    cellStyle0010.BorderBottom = BorderStyle.Medium;
                    cellStyle0010.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("0010", cellStyle0010);

                    var cellStyle0011 = wb.CreateCellStyle();
                    cellStyle0011.CloneStyleFrom(baseCellStyle);
                    cellStyle0011.BorderTop = BorderStyle.Thin;
                    cellStyle0011.BorderRight = BorderStyle.Thin;
                    cellStyle0011.BorderBottom = BorderStyle.Medium;
                    cellStyle0011.BorderLeft = BorderStyle.Medium;
                    rtnDic.Add("0011", cellStyle0011);

                    var cellStyle0001 = wb.CreateCellStyle();
                    cellStyle0001.CloneStyleFrom(baseCellStyle);
                    cellStyle0001.BorderTop = BorderStyle.Thin;
                    cellStyle0001.BorderRight = BorderStyle.Thin;
                    cellStyle0001.BorderBottom = BorderStyle.Thin;
                    cellStyle0001.BorderLeft = BorderStyle.Medium;
                    rtnDic.Add("0001", cellStyle0001);
                    #endregion

                    #region 列数大于1
                    var cellStyle1011 = wb.CreateCellStyle();
                    cellStyle1011.CloneStyleFrom(baseCellStyle);
                    cellStyle1011.BorderTop = BorderStyle.Medium;
                    cellStyle1011.BorderRight = BorderStyle.Thin;
                    cellStyle1011.BorderBottom = BorderStyle.Medium;
                    cellStyle1011.BorderLeft = BorderStyle.Medium;
                    rtnDic.Add("1011", cellStyle1011);

                    var cellStyle1010 = wb.CreateCellStyle();
                    cellStyle1010.CloneStyleFrom(baseCellStyle);
                    cellStyle1010.BorderTop = BorderStyle.Medium;
                    cellStyle1010.BorderRight = BorderStyle.Thin;
                    cellStyle1010.BorderBottom = BorderStyle.Medium;
                    cellStyle1010.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("1010", cellStyle1010);

                    var cellStyle1110 = wb.CreateCellStyle();
                    cellStyle1110.CloneStyleFrom(baseCellStyle);
                    cellStyle1110.BorderTop = BorderStyle.Medium;
                    cellStyle1110.BorderRight = BorderStyle.Medium;
                    cellStyle1110.BorderBottom = BorderStyle.Medium;
                    cellStyle1110.BorderLeft = BorderStyle.Thin;
                    rtnDic.Add("1110", cellStyle1110);
                    #endregion

                    break;
                case BorderType.None:
                default:
                    break;
            }

            return rtnDic;
        }
    }
}
